﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfSnqkGasAnalysis.IData;
using WpfSnqkGasAnalysis.Model;

namespace WpfSnqkGasAnalysis.IData_impl
{
    /// <summary>
    /// 数据接口实现
    /// </summary>
    /// 创建时间：2022-12-13 23:25:04
    public class CleanGasDAL_impl : BaseDAL, ICleanGasDAL
    {
        public IResult Add(Clean_gas clean_Gas)
        {
            Snowflake snowflake = new Snowflake(1, 1);
            long mainKey = snowflake.NextId();
            string sql = $@"INSERT INTO clean_gas (
id,
sample_no,
well_name,
sampling_location,
air_source,
original_sampling_position,
net_sampling_position,
sampling_temperature,
sampling_time,
analysis_time,
sampling_condiction,
sampling_user,
analysis_user,
analysis_company,
feedgas_percent,
feedgas_mg_per_m3,
clean_gas_percent,
clean_gas_mg_per_m3,
create_time,
input_user_id,
input_user,
proofreader,
proofread_time,
o_step
)
VALUES(
{ mainKey},
$sample_no,
$well_name,
$sampling_location,
$air_source,
$original_sampling_position,
$net_sampling_position,
$sampling_temperature,
$sampling_time,
$analysis_time,
$sampling_condiction,
$sampling_user,
$analysis_user,
$analysis_company,
$feedgas_percent,
$feedgas_mg_per_m3,
$clean_gas_percent,
$clean_gas_mg_per_m3,
'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}',
$input_user_id,
$input_user,
$proofreader,
$proofread_time,
$o_step
);
     ";
            Dictionary<string, object> keyValuePairs = new Dictionary<string, object>();
            keyValuePairs.Add("$sample_no", clean_Gas.Sample_no);
            keyValuePairs.Add("$well_name", clean_Gas.Well_name);
            keyValuePairs.Add("$sampling_location", clean_Gas.Sampling_location);
            keyValuePairs.Add("$air_source", clean_Gas.Air_source);
            keyValuePairs.Add("$original_sampling_position", clean_Gas.Original_sampling_position);
            keyValuePairs.Add("$net_sampling_position", clean_Gas.Net_sampling_position);
            keyValuePairs.Add("$sampling_temperature", clean_Gas.Sampling_temperature);
            keyValuePairs.Add("$sampling_time", clean_Gas.Sampling_time);
            keyValuePairs.Add("$analysis_time", clean_Gas.Analysis_time);
            keyValuePairs.Add("$sampling_condiction", clean_Gas.Sampling_condiction);
            keyValuePairs.Add("$sampling_user", clean_Gas.Sampling_user);
            keyValuePairs.Add("$analysis_user", clean_Gas.Analysis_user);
            keyValuePairs.Add("$analysis_company", clean_Gas.Analysis_company);
            keyValuePairs.Add("$feedgas_percent", clean_Gas.Feedgas_percent);
            keyValuePairs.Add("$feedgas_mg_per_m3", clean_Gas.Feedgas_mg_per_m3);
            keyValuePairs.Add("$clean_gas_percent", clean_Gas.Clean_gas_percent);
            keyValuePairs.Add("$clean_gas_mg_per_m3", clean_Gas.Clean_gas_mg_per_m3);
            keyValuePairs.Add("$input_user_id", clean_Gas.Input_user_id);
            keyValuePairs.Add("$input_user", clean_Gas.Input_user);
            keyValuePairs.Add("$proofreader", clean_Gas.Proofreader);
            keyValuePairs.Add("$proofread_time", clean_Gas.Proofread_time);
            keyValuePairs.Add("$o_step", clean_Gas.O_step);

            //在同一个sql中。处理保存，保证事务
            //clean_gas_update_log  
            sql += $@"INSERT INTO clean_gas_update_log (
                                     id,
                                     sample_no,
                                     well_name,
                                     sampling_location,
                                     air_source,
                                     original_sampling_position,
                                     net_sampling_position,
                                     sampling_temperature,
                                     sampling_time,
                                     analysis_time,
                                     sampling_condiction,
                                     sampling_user,
                                     analysis_user,
                                     analysis_company,
                                     feedgas_percent,
                                     feedgas_mg_per_m3,
                                     clean_gas_percent,
                                     clean_gas_mg_per_m3,
                                     create_time,                                 
                                     jing_hua_qi_key,                                    
									 e_stat
                                 )
                                 VALUES (
                                     {snowflake.NextId()},
                                     $sample_no,
                                     $well_name,
                                     $sampling_location,
                                     $air_source,
                                     $original_sampling_position,
                                     $net_sampling_position,
                                     $sampling_temperature,
                                     $sampling_time,
                                     $analysis_time,
                                     $sampling_condiction,
                                     $sampling_user,
                                     $analysis_user,
                                     $analysis_company,
                                     $feedgas_percent,
                                     $feedgas_mg_per_m3,
                                     $clean_gas_percent,
                                     $clean_gas_mg_per_m3,
                                    '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}',                                     
                                     {mainKey},                                     
                                     1
                                 );
";
            keyValuePairs.Add("$A_sample_no", clean_Gas.Sample_no);
            keyValuePairs.Add("$A_well_name", clean_Gas.Well_name);
            keyValuePairs.Add("$A_sampling_location", clean_Gas.Sampling_location);
            keyValuePairs.Add("$A_air_source", clean_Gas.Air_source);
            keyValuePairs.Add("$A_original_sampling_position", clean_Gas.Original_sampling_position);
            keyValuePairs.Add("$A_net_sampling_position", clean_Gas.Net_sampling_position);
            keyValuePairs.Add("$A_sampling_temperature", clean_Gas.Sampling_temperature);
            keyValuePairs.Add("$A_sampling_time", clean_Gas.Sampling_time);
            keyValuePairs.Add("$A_analysis_time", clean_Gas.Analysis_time);
            keyValuePairs.Add("$A_sampling_condiction", clean_Gas.Sampling_condiction);
            keyValuePairs.Add("$A_sampling_user", clean_Gas.Sampling_user);
            keyValuePairs.Add("$A_analysis_user", clean_Gas.Analysis_user);
            keyValuePairs.Add("$A_analysis_company", clean_Gas.Analysis_company);
            keyValuePairs.Add("$A_feedgas_percent", clean_Gas.Feedgas_percent);
            keyValuePairs.Add("$A_feedgas_mg_per_m3", clean_Gas.Feedgas_mg_per_m3);
            keyValuePairs.Add("$A_clean_gas_percent", clean_Gas.Clean_gas_percent);
            keyValuePairs.Add("$A_clean_gas_mg_per_m3", clean_Gas.Clean_gas_mg_per_m3);
            string msg = null;
            int count = ExecSqlCmd(sql, keyValuePairs, c =>
            {
                return c.ExecuteNonQuery();
            }, exp =>
            {
                msg = exp.Message;
            });
            if (count > 0)
            {
                return new ResultSuccess("操作成功", mainKey);
            }
            return new Result("新增失败，" + msg);
        }

        /// <summary>
        /// 编辑一条记录
        /// </summary>
        /// <param name="clean_Gas"></param>
        /// <returns></returns>
        /// 创建时间：2022-12-14 15:36:19。作者：王浩力
        public IResult Edit(Clean_gas clean_Gas)
        {
            long mainKey = clean_Gas.Id;
            string sql = $@"update  clean_gas  
SET 
sample_no=$sample_no,
well_name=$well_name,
sampling_location=$sampling_location,
air_source=$air_source,
original_sampling_position=$original_sampling_position,
net_sampling_position=$net_sampling_position,
sampling_temperature=$sampling_temperature,
sampling_time=$sampling_time,
analysis_time=$analysis_time,
sampling_condiction=$sampling_condiction,
sampling_user=$sampling_user,
analysis_user=$analysis_user,
analysis_company=$analysis_company,
feedgas_percent=$feedgas_percent,
feedgas_mg_per_m3=$feedgas_mg_per_m3,
Profread_user_id=$Profread_user_id,
clean_gas_percent=$clean_gas_percent,
clean_gas_mg_per_m3=$clean_gas_mg_per_m3
where  id={mainKey}  ;   ";
            Dictionary<string, object> keyValuePairs = new Dictionary<string, object>();
            keyValuePairs.Add("$sample_no", clean_Gas.Sample_no);
            keyValuePairs.Add("$well_name", clean_Gas.Well_name);
            keyValuePairs.Add("$sampling_location", clean_Gas.Sampling_location);
            keyValuePairs.Add("$air_source", clean_Gas.Air_source);
            keyValuePairs.Add("$original_sampling_position", clean_Gas.Original_sampling_position);
            keyValuePairs.Add("$net_sampling_position", clean_Gas.Net_sampling_position);
            keyValuePairs.Add("$sampling_temperature", clean_Gas.Sampling_temperature);
            keyValuePairs.Add("$sampling_time", clean_Gas.Sampling_time);
            keyValuePairs.Add("$analysis_time", clean_Gas.Analysis_time);
            keyValuePairs.Add("$sampling_condiction", clean_Gas.Sampling_condiction);
            keyValuePairs.Add("$sampling_user", clean_Gas.Sampling_user);
            keyValuePairs.Add("$analysis_user", clean_Gas.Analysis_user);
            keyValuePairs.Add("$analysis_company", clean_Gas.Analysis_company);
            keyValuePairs.Add("$feedgas_percent", clean_Gas.Feedgas_percent);
            keyValuePairs.Add("$feedgas_mg_per_m3", clean_Gas.Feedgas_mg_per_m3);
            keyValuePairs.Add("$Profread_user_id", clean_Gas.Profread_user_id);
            keyValuePairs.Add("$clean_gas_percent", clean_Gas.Clean_gas_percent);
            keyValuePairs.Add("$clean_gas_mg_per_m3", clean_Gas.Clean_gas_mg_per_m3);

            string msg = null;
            int count = ExecSqlCmd(sql, keyValuePairs, c =>
            {
                return c.ExecuteNonQuery();
            }, exp =>
            {
                msg = exp.Message;
            });
            if (count > 0)
            {
                return new ResultSuccess("编辑成功", mainKey);
            }
            return new Result("编辑失败，" + msg);
        }

        public IResult Delete(long clean_GasKEY, bool physicalDeletion = false)
        {
            string sql = $"update   Clean_gas  set  del_time='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}'  where id=" + clean_GasKEY;
            string msg = "";
            if (physicalDeletion)
            {
                msg = "物理";
                sql = "delete from  Clean_gas  where id=" + clean_GasKEY;
            }
            string error = null;
            int count = ExecSqlCmd(sql, null, c =>
            {
                return c.ExecuteNonQuery();
            }, exp =>
            {
                error = exp.Message;
            });
            if (count > 0)
            {
                return new ResultSuccess(msg + "删除成功");
            }
            return new Result(msg + "删除失败," + error);
        }

        public Clean_gas Get(long clean_GasKEY)
        {
            string sql = "select  *   from  Clean_gas  where id=" + clean_GasKEY;
            return base.ExecSqlCmd(sql, null, c =>
            {
                var reader = c.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                Clean_gas info = new Clean_gas();
                if (reader.Read())
                {
                    DataLoadInField(reader, info);
                }
                return info;
            });

        }

        /// <summary>
        /// 将表字段读取的数据装入实体
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="info"></param>
        private static void DataLoadInField(System.Data.Common.DbDataReader reader, Clean_gas info)
        {
            info.Id = reader.GetInt64(0);
            info.Sample_no = reader["sample_no"].ToString();
            info.Well_name = reader["well_name"].ToString();
            info.Sampling_location = reader["sampling_location"].ToString();
            info.Air_source = reader["air_source"].ToString();
            info.Original_sampling_position = reader["original_sampling_position"].ToString();
            info.Net_sampling_position = reader["net_sampling_position"].ToString();
            info.Sampling_temperature = reader["sampling_temperature"].ToMyDecimal();
            info.Sampling_time = reader["sampling_time"].ToString();
            info.Analysis_time = reader["analysis_time"].ToString();
            info.Sampling_condiction = reader["sampling_condiction"].ToString();
            info.Sampling_user = reader["sampling_user"].ToString();
            info.Analysis_user = reader["analysis_user"].ToMyInt64();
            info.Analysis_company = reader["analysis_company"].ToString();

            info.Feedgas_percent = reader["feedgas_percent"].ToString().ToMyDecimal();
            info.Feedgas_mg_per_m3 = reader["feedgas_mg_per_m3"].ToString().ToMyDecimal();
            info.Clean_gas_percent = reader["clean_gas_percent"].ToString().ToMyDecimal();
            info.Clean_gas_mg_per_m3 = reader["clean_gas_mg_per_m3"].ToString().ToMyDecimal();

            info.Create_time = reader["create_time"].ToString();
            info.Input_user_id = (long)reader["input_user_id"].ToString().ToMyDecimal();
            info.Input_user = reader["input_user"].ToString();
            info.Del_time = reader["del_time"].ToString();

            info.Profread_user_id = reader["Profread_user_id"].ToMyInt64();
            info.Proofreader = reader["proofreader"].ToString();
            info.Proofread_time = reader["proofread_time"].ToString();
            info.O_step = (byte)reader["o_step"].ToString().ToMyDecimal();
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="where"></param>
        /// <param name="p"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        /// 创建时间：2022-12-14 09:38:05。作者：王浩力
        public PageModel<Clean_gas> Page(Clean_gas where, int p, int rows)
        {
            PageModel<Clean_gas> pageModel = new PageModel<Clean_gas>();
            string sql = @"select *  
,(select check_user_name from clean_gas_check_history where jing_hua_qi_key=c.id  order by id desc limit 1) as check_user_name   
,(select check_time            from clean_gas_check_history where jing_hua_qi_key=c.id  order by id desc limit 1) as check_time   
   
                 from Clean_gas as c  where  del_time is null    ";
            StringBuilder whereSb = new StringBuilder();
            Dictionary<string, object> keyValuePairs = new Dictionary<string, object>();
            if (where != null)
            {
                if (!string.IsNullOrWhiteSpace(where.Sample_no))
                {
                    whereSb.Append("  and  Sample_no  like  $Sample_no ");
                    keyValuePairs.Add("$Sample_no", "%" + where.Sample_no + "%");
                }
                if (!string.IsNullOrWhiteSpace(where.E3) && DateTime.TryParse(where.E3, out DateTime d1))
                {
                    whereSb.Append($"  and  create_time >='{d1.ToString("yyyy-MM-dd HH:mm:ss")}' ");
                }
                if (!string.IsNullOrWhiteSpace(where.E4) && DateTime.TryParse(where.E4, out DateTime d2))
                {
                    whereSb.Append($"  and  create_time<='{d2.ToString("yyyy-MM-dd HH:mm:ss")}' ");
                }

                //步骤值查询 //添加时间：2022-12-29 16:23:29
                if (where.WorkList?.Count > 0)
                {
                    List<string> stepList = new List<string>();
                    foreach (var item in where.WorkList)
                    {
                        stepList.Add($"  o_step={item} ");
                    }
                    string step = $"  and  ({ string.Join(" or  ", stepList)  })  ";
                    whereSb.AppendLine(step);
                }
            }
            //查询总记录数
            string sqlTotal = "select  count(1) from  Clean_gas  where  del_time is null    ";
            sqlTotal += whereSb.ToString();
            pageModel.Total = ExecSqlCmd(sqlTotal, keyValuePairs, c =>
            {
                var obj = c.ExecuteScalar();
                return (int)obj.ToString().ToMyDecimal();
            });

            //查询页数据
            sql += whereSb.ToString()
                + $"  order by  id  desc  limit   {rows}  offset  {(p - 1) * rows}    ";

            pageModel.List = ExecSqlCmd(sql, keyValuePairs, c =>
            {
                var reader = c.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                List<Clean_gas> list = new List<Clean_gas>();
                while (reader.Read())
                {
                    Clean_gas info = new Clean_gas();
                    DataLoadInField(reader, info);

                    //审核人。2022-12-21 14:58:27
                    info.E3 = reader["check_user_name"].ToString();

                    //审核时间
                    info.E4 = reader["check_time"].ToString();

                    list.Add(info);
                }
                return list;
            });

            pageModel.PageIndex = p;
            pageModel.PageSize = rows;
            return pageModel;
        }

        /// <summary>
        /// 校对处理
        /// </summary>
        /// <param name="clean_Gas"></param>
        /// <param name="isEdited">是否修改了值。修改了则传true，否则false</param>
        /// <returns></returns>
        /// 创建时间：2022-12-14 16:53:13。作者：王浩力
        public IResult Proofread(Clean_gas clean_Gas, bool isEdited = false)
        {
            Snowflake snowflake = new Snowflake(1, 1);
            Dictionary<string, object> keyValuePairs = new Dictionary<string, object>();
            clean_Gas.Proofread_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
          
            //校对
            string sql = $"update  Clean_gas  set  o_step=2,Profread_user_id=$Profread_user_id,proofreader=$proofreader,proofread_time='{ clean_Gas.Proofread_time}'   where  id={clean_Gas.Id};  ";
            if (isEdited)
            {
                //修改了值
                sql = $@"update    Clean_gas  
SET 
sample_no=$sample_no,
well_name=$well_name,
sampling_location=$sampling_location,
air_source=$air_source,
original_sampling_position=$original_sampling_position,
net_sampling_position=$net_sampling_position,
sampling_temperature=$sampling_temperature,
sampling_time=$sampling_time,
analysis_time=$analysis_time,
sampling_condiction=$sampling_condiction,
sampling_user=$sampling_user,
analysis_user=$analysis_user,
analysis_company=$analysis_company,
feedgas_percent=$feedgas_percent,
feedgas_mg_per_m3=$feedgas_mg_per_m3,
clean_gas_percent=$clean_gas_percent,
clean_gas_mg_per_m3=$clean_gas_mg_per_m3,
proofreader=$proofreader,
proofread_time=$proofread_time,
Profread_user_id=$Profread_user_id,
o_step=2
where  id={clean_Gas.Id}  ;   ";

                keyValuePairs.Add("$sample_no", clean_Gas.Sample_no);
                keyValuePairs.Add("$well_name", clean_Gas.Well_name);
                keyValuePairs.Add("$sampling_location", clean_Gas.Sampling_location);
                keyValuePairs.Add("$air_source", clean_Gas.Air_source);
                keyValuePairs.Add("$original_sampling_position", clean_Gas.Original_sampling_position);
                keyValuePairs.Add("$net_sampling_position", clean_Gas.Net_sampling_position);
                keyValuePairs.Add("$sampling_temperature", clean_Gas.Sampling_temperature);
                keyValuePairs.Add("$sampling_time", clean_Gas.Sampling_time);
                keyValuePairs.Add("$analysis_time", clean_Gas.Analysis_time);
                keyValuePairs.Add("$sampling_condiction", clean_Gas.Sampling_condiction);
                keyValuePairs.Add("$sampling_user", clean_Gas.Sampling_user);
                keyValuePairs.Add("$analysis_user", clean_Gas.Analysis_user);
                keyValuePairs.Add("$analysis_company", clean_Gas.Analysis_company);
                keyValuePairs.Add("$feedgas_percent", clean_Gas.Feedgas_percent);
                keyValuePairs.Add("$feedgas_mg_per_m3", clean_Gas.Feedgas_mg_per_m3);
                keyValuePairs.Add("$clean_gas_percent", clean_Gas.Clean_gas_percent);
                keyValuePairs.Add("$clean_gas_mg_per_m3", clean_Gas.Clean_gas_mg_per_m3);
                keyValuePairs.Add("$proofreader", clean_Gas.Proofreader);
                keyValuePairs.Add("$proofread_time", clean_Gas.Proofread_time);
                keyValuePairs.Add("$Profread_user_id", clean_Gas.Profread_user_id);

                //修改日志存档
                //在同一个sql中。处理保存，保证事务
                //clean_gas_update_log  
                sql += $@"INSERT INTO clean_gas_update_log (
                                     id,
                                     sample_no,
                                     well_name,
                                     sampling_location,
                                     air_source,
                                     original_sampling_position,
                                     net_sampling_position,
                                     sampling_temperature,
                                     sampling_time,
                                     analysis_time,
                                     sampling_condiction,
                                     sampling_user,
                                     analysis_user,
                                     analysis_company,
                                     feedgas_percent,
                                     feedgas_mg_per_m3,
                                     clean_gas_percent,
                                     clean_gas_mg_per_m3,
                                     create_time,   
                                     update_real_name,
                                     jing_hua_qi_key,                                    
									 e_stat
                                 )
                                 VALUES (
                                     {snowflake.NextId()},
                                     $A_sample_no,
                                     $A_well_name,
                                     $A_sampling_location,
                                     $A_air_source,
                                     $A_original_sampling_position,
                                     $A_net_sampling_position,
                                     $A_sampling_temperature,
                                     $A_sampling_time,
                                     $A_analysis_time,
                                     $A_sampling_condiction,
                                     $A_sampling_user,
                                     $A_analysis_user,
                                     $A_analysis_company,
                                     $A_feedgas_percent,
                                     $A_feedgas_mg_per_m3,
                                     $A_clean_gas_percent,
                                     $A_clean_gas_mg_per_m3,
                                    '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}',                                     
                                     $A_update_real_name,
                                     {clean_Gas.Id},                 
                                     2
                                 );
";
                keyValuePairs.Add("$A_sample_no", clean_Gas.Sample_no);
                keyValuePairs.Add("$A_well_name", clean_Gas.Well_name);
                keyValuePairs.Add("$A_sampling_location", clean_Gas.Sampling_location);
                keyValuePairs.Add("$A_air_source", clean_Gas.Air_source);
                keyValuePairs.Add("$A_original_sampling_position", clean_Gas.Original_sampling_position);
                keyValuePairs.Add("$A_net_sampling_position", clean_Gas.Net_sampling_position);
                keyValuePairs.Add("$A_sampling_temperature", clean_Gas.Sampling_temperature);
                keyValuePairs.Add("$A_sampling_time", clean_Gas.Sampling_time);
                keyValuePairs.Add("$A_analysis_time", clean_Gas.Analysis_time);
                keyValuePairs.Add("$A_sampling_condiction", clean_Gas.Sampling_condiction);
                keyValuePairs.Add("$A_sampling_user", clean_Gas.Sampling_user);
                keyValuePairs.Add("$A_analysis_user", clean_Gas.Analysis_user);
                keyValuePairs.Add("$A_analysis_company", clean_Gas.Analysis_company);
                keyValuePairs.Add("$A_feedgas_percent", clean_Gas.Feedgas_percent);
                keyValuePairs.Add("$A_feedgas_mg_per_m3", clean_Gas.Feedgas_mg_per_m3);
                keyValuePairs.Add("$A_clean_gas_percent", clean_Gas.Clean_gas_percent);
                keyValuePairs.Add("$A_clean_gas_mg_per_m3", clean_Gas.Clean_gas_mg_per_m3);
                keyValuePairs.Add("$A_update_real_name", clean_Gas.Proofreader);
            }
            else
            {
                keyValuePairs.Add("$proofreader", clean_Gas.Proofreader);
                keyValuePairs.Add("$Profread_user_id", clean_Gas.Profread_user_id);
            }
            string error = null;
            int count = ExecSqlCmd(sql, keyValuePairs, c =>
            {
                return c.ExecuteNonQuery();
            }, exp =>
            {
                error = exp.Message;
            });
            if (count > 0)
            {
                return new ResultSuccess("校对处理成功");
            }
            return new Result("校对处理出错," + error);
        }


        /// <summary>
        /// 统计样本数量，审核通过的，没有被删除的
        /// </summary>
        /// 创建时间：2023-1-3 09:48:59。作者：王浩力
        public int GetCleanCount()
        {
            string sql = @$" SELECT  count(1)  FROM  clean_gas  where    o_step = 4  and  del_time is null  ";
            return ExecSqlCmd(sql, null, c =>
            {
                long va = (long)c.ExecuteScalar().ToMyInt64();
                return (int)va;
            });
        }

    }
}
